use log::debug;
use rusqlite::{params, ToSql};
use tauri::State;

use crate::error::error::MyError;
use crate::model::contact::*;
use crate::model::db_connection::DatabaseConnection;
use crate::model::output::Output;
use crate::model::pagination::Pagination;

#[tauri::command(rename_all = "snake_case")]
pub fn create_contact(
    data: &str,
    state: State<DatabaseConnection>,
) -> Result<Output<Contact>, MyError> {
    debug!("create_contact: {}", data);
    let contact: Contact = serde_json::from_str(data).map_err(|e| {
        MyError::new(
            1000,
            "Parse JSON error when create contact".to_string(),
            e.to_string(),
        )
    })?;

    state
        .conn
        .lock()
        .unwrap()
        .execute(
            r#"INSERT INTO contact (
        name, email, phone, gender, birthday, job_title, avatar, company_id,
        status, personality, created_by, modified_by)
        VALUES
        (
            ?,?,?,?,?,?,?,?,?,?,?,?
        )"#,
            params![
                contact.name,
                contact.email,
                contact.phone,
                contact.gender,
                contact.birthday,
                contact.job_title,
                contact.avatar,
                contact.company_id,
                contact.status,
                contact.personality,
                "System",
                "System"
            ],
        )
        .map_err(|e| MyError::new(1002, "Insert contact error".to_string(), e.to_string()))?;

    return Ok(Output::success("Ok".to_string(), None));
}

#[tauri::command(rename_all = "snake_case")]
pub fn update_contact(
    data: &str,
    state: State<DatabaseConnection>,
) -> Result<Output<Contact>, MyError> {
    let contact: Contact = serde_json::from_str(data).map_err(|e| {
        MyError::new(
            1000,
            "Parse JSON error when update contact".to_string(),
            e.to_string(),
        )
    })?;

    let sql = r#"
        UPDATE contact
        SET
        name = ?1,
        email = ?2,
        phone = ?3,
        gender = ?4,
        birthday = ?5,
        job_title = ?6,
        avatar = ?7,
        company_id = ?8,
        status = ?9,
        personality = ?10,
        modified_by = ?11
        WHERE id = ?12
    "#;

    let conn = state.conn.lock().unwrap();
    let affected_rows = conn
        .execute(
            sql,
            params![
                contact.name,
                contact.email,
                contact.phone,
                contact.gender,
                contact.birthday,
                contact.job_title,
                contact.avatar,
                contact.company_id,
                contact.status,
                contact.personality,
                "system".to_string(),
                contact.id,
            ],
        )
        .map_err(|e| MyError::database_error("Update contact failed".to_string(), e.to_string()))?;

    if affected_rows == 0 {
        return Err(MyError::new(
            404,
            "contact not found".to_string(),
            "".to_string(),
        ));
    }

    // 返回更新后的数据（可选）
    Ok(Output::success(
        "Update successful".to_string(),
        Some(contact),
    ))
}

#[tauri::command(rename_all = "snake_case")]
pub fn list_contact(
    contact_name: Option<String>,
    company_id: Option<i32>,
    status: Option<i32>,
    page_current: i32, // 当前页码，从1开始
    page_size: i32,    // 每页大小
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Pagination<Contact>>, MyError> {
    debug!("list_contact: contact_name={:?}, company_id={:?}, status={:?}, page_current={}, page_size={}",
        contact_name, company_id, status, page_current, page_size);
    // 构建基础查询和条件
    let mut sql =
        r#"SELECT c.id,c.name, email, phone, gender, birthday, job_title,  avatar, company_id,
                    c.status, personality, c.created_by, c.created_at, c.modified_by, c.modified_at, co.name as company_name
               FROM contact c left join company co on c.company_id = co.id WHERE 1=1"#
            .to_string();

    let mut count_sql = "SELECT COUNT(*) FROM contact c WHERE 1=1".to_string(); // 修改 COUNT 查询也使用别名
    let mut params: Vec<&dyn ToSql> = Vec::new();
    let mut conditions = Vec::new();

    // 使用局部变量存储参数值
    let name_param = contact_name.as_ref().map(|name| format!("%{}%", name));

    // 添加过滤条件 - 修复 Option 类型的处理
    if let Some(ref name) = name_param {
        conditions.push(" c.name LIKE ? ");
        params.push(name);
    }

    if let Some(ref id) = company_id {
        conditions.push(" c.company_id = ? "); // 添加表别名
        params.push(id);
    }

    if let Some(ref status) = status {
        conditions.push(" c.status = ? "); // 添加表别名，明确使用 contact 表的 status
        params.push(status);
    }

    // 拼接WHERE条件
    if !conditions.is_empty() {
        let where_clause = conditions.join(" AND ");
        sql.push_str(" AND ");
        sql.push_str(&where_clause);
        count_sql.push_str(" AND ");
        count_sql.push_str(&where_clause);
    }

    // 添加分页
    let offset = (page_current - 1) * page_size;
    sql.push_str(" ORDER BY c.id LIMIT ? OFFSET ? ");

    // 保存条件参数的数量，用于 COUNT 查询
    let condition_params_len = params.len();

    // 添加分页参数
    params.push(&page_size);
    params.push(&offset);

    let conn = state.conn.lock().unwrap();

    // 查询总数 - 只使用条件参数，不包括分页参数
    let total_count: i32 = conn
        .query_row(&count_sql, &params[..condition_params_len], |row| {
            row.get(0)
        })
        .map_err(|e| {
            MyError::database_error(
                "Count query error in list_contacts".to_string(),
                e.to_string(),
            )
        })?;

    // 计算总页数
    let page_total = if total_count == 0 {
        0
    } else {
        (total_count + page_size - 1) / page_size
    };

    // 查询分页数据
    let mut stmt = conn.prepare(&sql).map_err(|e| {
        MyError::database_error(
            "Prepare statement error in list_contacts".to_string(),
            e.to_string(),
        )
    })?;

    let rows = stmt
        .query_map(&*params, |row| {
            Ok(Contact {
                id: row.get(0)?,
                name: row.get(1)?,
                email: row.get(2)?,
                phone: row.get(3)?,
                gender: row.get(4)?,
                birthday: row.get(5)?,
                job_title: row.get(6)?,
                avatar: row.get(7)?,
                company_id: row.get(8)?,
                status: row.get(9)?,
                personality: row.get(10)?,
                created_by: row.get(11)?,
                created_at: row.get(12)?,
                modified_by: row.get(13)?,
                modified_at: row.get(14)?,
                company_name: row.get(15)?,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query mapping error in list_contacts".to_string(),
                e.to_string(),
            )
        })?;

    let mut contact_list = Vec::new();
    for row in rows {
        let contact = row.map_err(|e| {
            MyError::database_error(
                "Row mapping error in list_contacts".to_string(),
                e.to_string(),
            )
        })?;
        contact_list.push(contact);
    }

    // 构建分页响应
    let pagination = Pagination {
        page_size,
        page_total,
        page_current,
        page_items: contact_list,
    };

    Ok(Output::success("OK".to_string(), Some(pagination)))
}

#[tauri::command(rename_all = "snake_case")]
pub fn list_contact_by_company(
    company_id: i32,
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Vec<Contact>>, MyError> {
    let (sql, params): (&str, Vec<&dyn ToSql>) = {
        (
            r#"SELECT c.id, c.name, email, phone, gender, birthday, job_title, avatar, company_id,
            c.status, personality, c.created_by, c.created_at, c.modified_by, c.modified_at,co.name as company_name
               FROM contact c left join company co on c.company_id = co.id where company_id = ? and c.status in (1,2)"#,
            vec![&company_id],
        )
    };

    let conn = state.conn.lock().unwrap();
    let mut stmt = conn.prepare(sql).map_err(|e| {
        MyError::database_error(
            "Prepare statement error in list_contact".to_string(),
            e.to_string(),
        )
    })?;

    let rows = stmt
        .query_map(&*params, |row| {
            Ok(Contact {
                id: row.get(0)?,
                name: row.get(1)?,
                email: row.get(2)?,
                phone: row.get(3)?,
                gender: row.get(4)?,
                birthday: row.get(5)?,
                job_title: row.get(6)?,
                avatar: row.get(7)?,
                company_id: row.get(8)?,
                status: row.get(9)?,
                personality: row.get(10)?,
                created_by: row.get(11)?,
                created_at: row.get(12)?,
                modified_by: row.get(13)?,
                modified_at: row.get(14)?,
                company_name: row.get(15)?,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query mapping error in list_contact".to_string(),
                e.to_string(),
            )
        })?;

    let mut contact_list = Vec::new();
    for row in rows {
        let contact = row.map_err(|e| {
            MyError::database_error(
                "Row mapping error in list_contact".to_string(),
                e.to_string(),
            )
        })?;
        contact_list.push(contact);
    }

    Ok(Output::success("OK".to_string(), Some(contact_list)))
}

#[tauri::command(rename_all = "snake_case")]
pub fn contact_detail(
    contact_id: i32,
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Contact>, MyError> {
    let conn = state.conn.lock().unwrap();

    let sql = "SELECT  c.id,c.name, email, phone, gender, birthday, job_title, avatar, company_id,
        c.status, personality, c.created_by, c.created_at, c.modified_by, c.modified_at, company.name as company_name
       FROM contact c left join company on c.company_id = company.id WHERE c.id = ?";

    let mut stmt = conn.prepare(sql).map_err(|e| {
        MyError::database_error(
            "Prepare statement error in contact_detail".to_string(),
            e.to_string(),
        )
    })?;

    let row = stmt
        .query_row(params![contact_id], |row| {
            Ok(Contact {
                id: row.get(0)?,
                name: row.get(1)?,
                email: row.get(2)?,
                phone: row.get(3)?,
                gender: row.get(4)?,
                birthday: row.get(5)?,
                job_title: row.get(6)?,
                avatar: row.get(7)?,
                company_id: row.get(8)?,
                status: row.get(9)?,
                personality: row.get(10)?,
                created_by: row.get(11)?,
                created_at: row.get(12)?,
                modified_by: row.get(13)?,
                modified_at: row.get(14)?,
                company_name: row.get(15)?,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query row error in contact_detail".to_string(),
                e.to_string(),
            )
        })?;

    //todo!("query contracts ");

    Ok(Output::success("OK".to_string(), Some(row)))
}
